//数据库处理模块
let db = require("mysql");
//创建数据库连接
function getConn(host='localhost',user='root',pwd='root',database='store'){
    let conn = db.createConnection({
        host     : host,
        user     : user,
        password : pwd,
        database : database
    });
    conn.connect();
    return conn;  //返回连接
}
//导出数据库方法
module.exports = {
    add:function(table,cols,vals){
        return new Promise((resolve,reject)=>{
            //获取数据库连接
            let conn = getConn();
            //SQL
            let sql = "INSERT INTO `"+table+"`(";
            sql += cols.join();
            sql += ") VALUES('";
            sql += vals.join("','");
            sql += "')";
            //执行SQL
            conn.query(sql, function (error, results, fields) {
                if (error)
                    reject(error);
                //查看受影响的行数
                if(results.affectedRows){
                    console.log("新增成功...");
                    resolve(results.affectedRows);
                }
            });
            //关闭连接
            conn.end();
        })
    },
    search:function(table,where){
        return new Promise((resolve,reject)=>{
            //获取数据库连接
            let conn = getConn();
            /*
            {username:yss,pwd:123}
            */
            //SQL
            let sql = "SELECT * FROM `"+table+"`";
            if(typeof where == "object" && where != null){
                sql += " WHERE"
                for(let k in where){
                    sql += ` ${k} like '%${where[k]}%'`;
                }
            }
            //执行SQL
            conn.query(sql, function (error, results, fields) {
                if (error)
                    reject(error);
                //返回结果集
                resolve(results);
            });
            //关闭连接
            conn.end();
        })
    },

    update:function(table,data,where){
        return new Promise((resolve,reject)=>{
            //获取数据库连接
            let conn = getConn();
            /*
            {profile:"profile.jpg"}  --  data
            */
            //SQL
            let sql = "UPDATE `"+table+"`";
            //更新的字段
            if(typeof data == "object" && data != null){
                sql += " SET "
                for(let k in data){
                    sql += `${k}='${data[k]}',`;
                }
                sql = sql.slice(0,-1);
            }
            //更新的条件
            if(typeof where == "object" && where != null){
                sql += " WHERE"
                for(let k in where){
                    sql += ` ${k}='${where[k]}' AND`;
                }
                sql = sql.slice(0,-3);
            }
            console.log(sql);
            //执行SQL
            conn.query(sql, function (error, results, fields) {
                if (error)
                    reject(error);
                //返回结果集
                if(results.affectedRows>0){
                    console.log("头像保存到数据库成功");
                    resolve(results.affectedRows);
                }
            });
            //关闭连接
            conn.end();
        })
    },
    getAll:function(table){
        return new Promise((resolve,reject)=>{
            //获取数据库连接
            let conn = getConn();
            let sql = "SELECT * FROM `"+table+"`";
            console.log('mysql search..')
            console.log(sql);
            //执行SQL
            conn.query(sql, function (error, results, fields) {
                if (error)
                    reject(error);
                //返回结果集
                resolve(results);
            });
            //关闭连接
            conn.end();
        })
    },

}